package cmpe.swe.purcom.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

import cmpe.swe.purcom.dao.mapper.SkillRowMapper;
import cmpe.swe.purcom.model.Skill;

@Component
public class SkillDao extends PcDao {

	/**
	 * Returns a Long variable which includes the properties of new generated skill extension
	 * <p>
	 * These properties are set by using createPreparedStatement() function inside the method
	 * @param name the name of the new created skill
	 * @return a Long variable for item id
	 */
	public Long createSkill(final String name) {
		final String query = "INSERT INTO SKILL (NAME) VALUES(?)";

		KeyHolder gkh = new GeneratedKeyHolder();

		this.getTemplate().update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement ps = connection.prepareStatement(query,
						Statement.RETURN_GENERATED_KEYS);
				ps.setString(1, name);

				return ps;
			}
		}, gkh);

		Long newItemId = gkh.getKey().longValue();

		return newItemId;
	}

	/**
	 * Returns the Skill properties in a List. If there is no record in the database, it will return null
	 * @return a List of Skill
	 */
	public List<Skill> getSkills() {

		List<Skill> skills = this.getTemplate().query("SELECT * FROM SKILL",
				new SkillRowMapper());

		if (skills.isEmpty()) {
			return null;
		} else {
			return skills;
		}

	}
}
